package com.cz.modules.rll.controller;

import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.*;

import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.cz.common.utils.POIExcelUtils;
import com.cz.common.validator.ValidatorUtils;
import com.cz.modules.jxc.entity.JxcShopEntity;
import com.cz.modules.jxc.service.JxcShopService;
import com.cz.modules.rll.entity.RllPlaceEntity;
import com.cz.modules.rll.service.RllHourCountService;
import com.cz.modules.rll.service.RllPlaceService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.cz.modules.rll.entity.RllDayInfoEntity;
import com.cz.modules.rll.service.RllDayInfoService;
import com.cz.common.utils.PageUtils;
import com.cz.common.utils.R;

import javax.servlet.http.HttpServletResponse;


/**
 * 
 *
 * @author Cz
 * @email 
 * @date 2020-04-26 20:25:45
 */
@RestController
@RequestMapping("rll/rlldayinfo")
public class RllDayInfoController {
    @Autowired
    private RllDayInfoService rllDayInfoService;
    @Autowired
    private RllPlaceService rllPlaceService;
    @Autowired
    private RllHourCountService rllHourCountService;
    @Autowired
    private JxcShopService jxcShopService;

    /**
     * 导出
     */
    @RequestMapping("/upload")
    public void exportcll(@RequestParam Map<String, Object> params, HttpServletResponse response){

        String type= (String) params.get("type");
        String stime= (String) params.get("startTime");
        String etime= (String) params.get("endTime");
        Date dd=new Date();
        String date=DateUtil.format(dd,"yyyy-MM-dd");
        String startTime="";
        String endTime="";
        if("".equals(type) || "null".equals(type)){
            type="7";
        }
        Calendar calendar = Calendar.getInstance();
        if(!"".equals(stime) &&  !"null".equals(stime) && !"".equals(etime) &&  !"null".equals(etime)){//自定义时间
            startTime =stime+" 00:00:00";
            endTime =etime+" 23:59:59";
        }else{//无自定义时间
            if("7".equals(type)){//近7天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -6);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }else if("30".equals(type)){//近30天
                calendar.setTime(dd);
                calendar.add(Calendar.DAY_OF_MONTH, -29);
                startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                startTime +=" 00:00:00";
                endTime =date+" 23:59:59";
            }
        }
        List<JxcShopEntity> shopList=jxcShopService.list();
        List<JxcShopEntity> southShopList=new ArrayList<>();
        List<JxcShopEntity> northShopList=new ArrayList<>();
        for (JxcShopEntity shop:shopList) {
            if(shop.getDeptId().equals("3")){//南区
                southShopList.add(shop);
            }else if(shop.getDeptId().equals("4")){
                northShopList.add(shop);
            }
        }


        List srlllist=rllHourCountService.kllChartday("南区",startTime,endTime);//南区客流量 list.get(1)
        List nrlllist=rllHourCountService.kllChartday("北区",startTime,endTime);//北区客流量


        List<String> timeList = (List<String>)srlllist.get(0);
        List<Integer> rlls= (List<Integer>) srlllist.get(1);//南区客流
        List<Integer> rlln= (List<Integer>) nrlllist.get(1);



        String str[]={"日期","南区客流","北区客流","合计"};
        String fileName = "客流量数据统计"+new Date().getTime()+".xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("客流量统计");
        for (int i = 0; i <str.length ; i++) {
            sheet.setColumnWidth(i, (int)20*100);//列宽 excel中1字符约等于 此处2.8
        }
        // sheet.setColumnWidth(0, (int)19.3*100);//列宽 excel中1字符约等于 此处2.8
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));//合并单元格 第一行标题
        HSSFCellStyle style = workbook.createCellStyle();//样式  标题用

        //创建Excel工作表第一行
        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);

        row0.setHeight((short) 800);//行高 1磅=20short
        cell0.setCellValue(new HSSFRichTextString("盂县服务区客流量数据统计"));
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)12);//字体大小
        font.setBold(true);//加粗
        style.setFont(font);//将字体样式放入style
        font.setFontName("宋体");
        style.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        cell0.setCellStyle(style);//将style放入cell

        HSSFCellStyle style1 = workbook.createCellStyle();//样式1 第二行用
        HSSFFont font1 = workbook.createFont();
        font1.setFontHeightInPoints((short)12);//字体大小
        font1.setBold(false);//加粗
        font1.setFontName("宋体");
        style1.setFont(font1);//将字体样式放入style
        style1.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style1.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style1.setWrapText(true);//自动换行
        style1.setBorderBottom(BorderStyle.THIN); //下边框
        style1.setBorderLeft(BorderStyle.THIN);//左边框
        style1.setBorderTop(BorderStyle.THIN);//上边框
        style1.setBorderRight(BorderStyle.THIN);//右边框


        HSSFCellStyle style2 = workbook.createCellStyle();//样式2 带绿色背景颜色的
        style2.setFont(font1);//将字体样式放入style
        style2.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style2.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style2.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setWrapText(true);//自动换行
        style2.setBorderBottom(BorderStyle.THIN); //下边框
        style2.setBorderLeft(BorderStyle.THIN);//左边框
        style2.setBorderTop(BorderStyle.THIN);//上边框
        style2.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style3 = workbook.createCellStyle();//样式3 带红色背景颜色的 倒数第二行 合计用
        style3.setFont(font1);//将字体样式放入style
        style3.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style3.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style3.setFillForegroundColor(IndexedColors.RED.getIndex());
        style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style3.setBorderBottom(BorderStyle.THIN); //下边框
        style3.setBorderLeft(BorderStyle.THIN);//左边框
        style3.setBorderTop(BorderStyle.THIN);//上边框
        style3.setBorderRight(BorderStyle.THIN);//右边框

        HSSFCellStyle style4 = workbook.createCellStyle();//样式4 带蓝色背景颜色的 最后一行平均用
        style4.setFont(font1);//将字体样式放入style
        style4.setAlignment(HorizontalAlignment.CENTER);//左右居中
        style4.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        style4.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        style4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style4.setBorderBottom(BorderStyle.THIN); //下边框
        style4.setBorderLeft(BorderStyle.THIN);//左边框
        style4.setBorderTop(BorderStyle.THIN);//上边框
        style4.setBorderRight(BorderStyle.THIN);//右边框
        //第二行
        HSSFRow row1 = sheet.createRow(1);
        for (int i = 0; i <str.length ; i++) {
            HSSFCell cell1 = row1.createCell(i);
            row1.setHeight((short) 900); // 设置行高
            cell1.setCellValue(new HSSFRichTextString(str[i]));
            if(i == 3 || i == 6 || i == 9 || i == 12 || i == 15 || i==18 || i==21){
                cell1.setCellStyle(style2);//将style放入cell
            }else {
                cell1.setCellStyle(style1);//将style放入cell
            }
        }

        int totalrlls=0;
        int totalrlln=0;
        int totalrll=0;

        DecimalFormat df = new DecimalFormat("0.00");
        DecimalFormat df1 = new DecimalFormat("0");
        //创建内容所有行
        for (int i = 0; i <timeList.size() ; i++) {
            HSSFRow row= sheet.createRow(i+2);//创建行
            row.setHeight((short) 600); // 设置行高
            for (int j = 0; j <str.length ; j++) {
                HSSFCell cell = row.createCell(j);//创建单元格
                if(j ==0){//日期
                    cell.setCellValue(timeList.get(i));
                }else if(j ==1){//客流
                    totalrlls +=rlls.get(i);
                    cell.setCellValue(rlls.get(i));
                }else if(j ==2){
                    totalrlln +=rlln.get(i);
                    cell.setCellValue(rlln.get(i));
                }else if(j ==3){
                    totalrll +=(rlls.get(i)+rlln.get(i));
                    cell.setCellValue((rlls.get(i)+rlln.get(i)));
                }
                cell.setCellStyle(style1);
            }
        }
        //总计
        HSSFRow rowLast2= sheet.createRow(timeList.size()+2);//创建行
        rowLast2.setHeight((short) 600); // 设置行高
        for (int i = 0; i <str.length ; i++) {
            HSSFCell cell = rowLast2.createCell(i);//创建单元格
            if(i == 0){
                cell.setCellValue("合计");
            }else if(i == 1){
                cell.setCellValue(totalrlls);
            }else if(i == 2){
                cell.setCellValue(totalrlln);
            }else if(i == 3){
                cell.setCellValue(totalrll);
            }
            cell.setCellStyle(style3);
        }
        //平均
        HSSFRow rowLast= sheet.createRow(timeList.size()+3);//创建行
        rowLast.setHeight((short) 600); // 设置行高
        int size=timeList.size();
        if(size ==0){
            size =1;
        }
        for (int i = 0; i <str.length ; i++){
            HSSFCell cell = rowLast.createCell(i);//创建单元格
            if(i == 0){
                cell.setCellValue("平均");
            }else if(i == 1){
                String kgratio = df1.format((float)totalrlls/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 2){
                String kgratio = df1.format((float)totalrlln/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }else if(i == 3){
                String kgratio = df1.format((float)totalrll/size);
                Double dzg=Double.parseDouble(kgratio);
                cell.setCellValue(dzg);
            }
            cell.setCellStyle(style4);
        }
        try {
            fileName = new String(fileName.getBytes(),"ISO8859-1");
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+fileName);
            response.setContentType("application/msexcel");
            workbook.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 列表
     */
    @RequestMapping("/list")
    @RequiresPermissions("rll:rlldayinfo:list")
    public R list(@RequestParam Map<String, Object> params){
        PageUtils page = rllDayInfoService.queryPage(params);

        return R.ok().put("page", page);
    }


    /**
     * 信息
     */
    @RequestMapping("/info/{id}")
    @RequiresPermissions("rll:rlldayinfo:info")
    public R info(@PathVariable("id") String id){
        RllDayInfoEntity rllDayInfo = rllDayInfoService.getById(id);

        return R.ok().put("rllDayInfo", rllDayInfo);
    }

    /**
     * 保存
     */
    @RequestMapping("/save")
    @RequiresPermissions("rll:rlldayinfo:save")
    public R save(@RequestBody RllDayInfoEntity rllDayInfo){
        rllDayInfoService.save(rllDayInfo);

        return R.ok();
    }

    /**
     * 修改
     */
    @RequestMapping("/update")
    @RequiresPermissions("rll:rlldayinfo:update")
    public R update(@RequestBody RllDayInfoEntity rllDayInfo){
        ValidatorUtils.validateEntity(rllDayInfo);
        rllDayInfoService.updateById(rllDayInfo);
        
        return R.ok();
    }

    /**
     * 删除
     */
    @RequestMapping("/delete")
    @RequiresPermissions("rll:rlldayinfo:delete")
    public R delete(@RequestBody String[] ids){
        rllDayInfoService.removeByIds(Arrays.asList(ids));

        return R.ok();
    }


    /**
     * 删除
     */
    @RequestMapping("/kllCount")
    public R kllCount(@RequestParam Map<String, Object> params){

        String type= (String) params.get("type");
        String time= (String) params.get("time");
        //南区客流相机
        List<RllPlaceEntity> southList=rllPlaceService.list(new QueryWrapper<RllPlaceEntity>().eq("area","南区").eq("type","0"));
        List<RllPlaceEntity> northList=rllPlaceService.list(new QueryWrapper<RllPlaceEntity>().eq("area","北区").eq("type","0"));
        Integer southCount=0;
        Integer northCount=0;
        String startTime="";
        String endTime="";
        String date= DateUtil.format(new Date(),"yyyy-MM-dd");
        if(type==null && time==null){
            southCount=rllDayInfoService.kllTodayCount(southList);
            northCount=rllDayInfoService.kllTodayCount(northList);
        }else if(!"".equals(time) && time !=null){//有自定义时间
            if("0".equals(type)){//该自定义时间 的当天的起止时间的数据
                startTime =time+" 00:00:00";
                endTime =time+" 23:59:59";
            }else if("1".equals(type)){//该自定义时间 的当月的起止时间的数据
                startTime =time+"-01 00:00:00";
                Date dd=DateUtil.parse(startTime);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dd);
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
            }
            southCount=rllHourCountService.kllCount(startTime,endTime,southList);
            northCount=rllHourCountService.kllCount(startTime,endTime,northList);
        }else {//无自定义时间
            if("0".equals(type)){//当天的数据
                southCount=rllDayInfoService.kllTodayCount(southList);
                northCount=rllDayInfoService.kllTodayCount(northList);
            }else if("1".equals(type)){//当月的数据
                String d=DateUtil.format(new Date(),"yyyy-MM");
                startTime = d+"-01 00:00:00";
                // 获取当前月的最后一天
                Calendar calendar1 = Calendar.getInstance();
                calendar1.setTime(new Date());
                calendar1.add(Calendar.MONTH, 1);
                calendar1.set(Calendar.DAY_OF_MONTH, 0);
                endTime = DateUtil.format(calendar1.getTime(),"yyyy-MM-dd");
                endTime +=" 23:59:59";
                southCount=rllHourCountService.kllCount(startTime,endTime,southList);
                northCount=rllHourCountService.kllCount(startTime,endTime,northList);
            }
        }
        return R.ok().put("kllCount",String.valueOf(southCount+northCount).toCharArray()).put("southCount",southCount).put("northCount",northCount);
    }
    /**
     * 页面echart--hp
     */
    @RequestMapping("/rllChartHp")
    public R rllChartHp(@RequestParam Map<String, Object> params){
        String type= (String) params.get("type");
        String startTime= (String) params.get("startTime");
        String endTime= (String) params.get("endTime");
        Calendar calendar = Calendar.getInstance();
        if("0".equals(type)){ //近7天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -6);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }else if("1".equals(type)){//近30天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -29);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }
        List slist=rllHourCountService.kllChartday("南区",startTime,endTime);
        List nlist=rllHourCountService.kllChartday("北区",startTime,endTime);
        return R.ok().put("time",slist.get(0)).put("skll",slist.get(1)).put("nkll",nlist.get(1));
    }
    /**
     * 页面echart数据导出
     */
    @RequestMapping("/exportrll")
    public void exportrll(@RequestParam Map<String, Object> params, HttpServletResponse response){
        String type= (String) params.get("type");
        String startTime= (String) params.get("startTime");
        String endTime= (String) params.get("endTime");
        Calendar calendar = Calendar.getInstance();
        if("0".equals(type)){ //近7天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -6);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }else if("1".equals(type)){//近30天
            endTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
            calendar.add(Calendar.DAY_OF_MONTH, -29);
            startTime=DateUtil.format(calendar.getTime(),"yyyy-MM-dd");
        }
        List slist=rllHourCountService.kllChartday("南区",startTime,endTime);
        List nlist=rllHourCountService.kllChartday("北区",startTime,endTime);

        //创建excel导出
        String[] title = {"日期","南区客流量","北区客流量","总客流量","平均客流量"};
        // excel文件名
        String fileName = "客流量统计"+new Date().getTime()+".xls";
        // sheet名
        String sheetName = "客流量统计";
        List<String> timeList = (List<String>)slist.get(0);
        List<Integer> rlls = (List<Integer>)slist.get(1);
        List<Integer> rlln = (List<Integer>)nlist.get(1);
        String [][] content = new String[timeList.size()][5];
        for(int i=0;i<timeList.size();i++){
            content[i][0] = timeList.get(i);
            content[i][1] = String.valueOf(rlls.get(i));
            content[i][2] = String.valueOf(rlln.get(i));
            content[i][3] = String.valueOf((rlls.get(i) + rlln.get(i)));
        }
        int zongrll = 0;
        for(Integer inte:rlls){
            zongrll += inte;
        }
        for(Integer inte:rlln){
            zongrll += inte;
        }
        content[0][4] = zongrll/timeList.size()+"人/天";
        // 创建HSSFWorkbook
        HSSFWorkbook wb = POIExcelUtils.getHSSFWorkbook(sheetName, title,content, null);

        // 响应到客户端
        try {
            fileName = new String(fileName.getBytes(),"ISO8859-1");
            OutputStream output=response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename="+fileName);
            response.setContentType("application/msexcel");
            wb.write(output);
            output.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
